package de.fhb.itunes.repository.DataAccess;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;

import de.fhb.itunes.repository.WrongVoMap;
import de.fhb.itunes.repository.data.Episode_VO;
import de.fhb.itunes.repository.data.Film_VO;
import de.fhb.itunes.repository.data.Musik_VO;
import de.fhb.itunes.repository.jdbc.DataAccessException;
import de.fhb.itunes.repository.jdbc.JdbcDAOBase;

public class DAO_Episode extends JdbcDAOBase implements IDAO_Episode {

	@Override
	public Episode_VO getById(int id) {
		Episode_VO vo;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		int key = -1;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] medi,   [MediaFileSatz_Episode] epi WHERE medi.[ID] = epi.[ID] AND epi.istTVSendung = 1 AND epi.ID = ?";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			vo = (Episode_VO) transformSetToVO(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vo;
	}

	@Override
	public HashMap<Integer, Episode_VO> getByStaffelAndStaffelnummer(
			String staffel, int nummer) {
		List<Episode_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] medi, [MediaFileSatz_Episode] epi WHERE medi.[ID] = epi.[ID] AND epi.istTVSendung = 1 AND epi.Staffelnummer = ? ANd epi.Staffelname = ?";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, nummer);
			ps.setString(2, staffel);
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		HashMap<Integer, Episode_VO> ret= new HashMap<Integer, Episode_VO>();
		try{
			for (Episode_VO episode : vos) {
				ret.put(episode.getEpisodennummer(), episode);
			}
		} catch (Exception e){
			throw new WrongVoMap("Die Daten aus der DB konnten nicht in ein Episoden-Vo umgewandelt werden");
		}
		
		return ret;
	}

	@Override
	public List<Episode_VO> getAll() {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] medi, [MediaFileSatz_Episode] epi WHERE medi.[ID] = epi.[ID] AND epi.istTVSendung = 1";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		List<Episode_VO> ret= null;
		try{
			ret = (List<Episode_VO>)vos;
		} catch (Exception e){
			throw new WrongVoMap("Die Daten aus der DB konnten nicht in ein Episoden-Vo umgewandelt werden");
		}
		
		return ret;
	}
	

	@Override
	protected Object map2VO(ResultSet rs) throws SQLException {
		Episode_VO episode = new Episode_VO();
		episode.setAdded(rs.getDate("Added"));
		episode.setEpisodennummer(rs.getInt("Episodennummer"));
		episode.setGenre(rs.getString("Genre"));
		episode.setId(rs.getInt("ID"));
		episode.setLaenge(rs.getLong("Länge"));
		episode.setPfad(rs.getString("Pfad"));
		episode.setTitel(rs.getString("Titel"));
		episode.setTrackid(rs.getInt("TrackID"));
		episode.setStaffelname(rs.getString("Staffelname"));
		episode.setStaffelnummer(rs.getInt("Staffelnummer"));
		return episode;
	}

	@Override
	public List<Episode_VO> getlastAdded(int anzahl) {
		List<Episode_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT TOP (?) * FROM  [MediaFileSatz] media,   [MediaFileSatz_Episode] film WHERE media.id = film.id AND film.istTVSendung = 1 order by Added desc";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, anzahl);
			rs = ps.executeQuery();
			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

	@Override
	public List<Episode_VO> searchbyKeyword(String keyword) {
		List vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT * FROM  [MediaFileSatz] media,   itunes.[dbo].MediaFileSatz_Episode epi WHERE media.id = epi.id AND (epi.Staffelname like ? OR media.Titel = ? )";
		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setString(1, "%" + keyword + "%");
			ps.setString(2, "%" + keyword + "%");
			rs = ps.executeQuery();

			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}
	public List<Episode_VO> getLastAdded(int anzahl) {
		List<Episode_VO> vos;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		String sqlStr = "SELECT TOP (?) * FROM  [MediaFileSatz] media,   itunes.[dbo].MediaFileSatz_Episode epi WHERE media.id = epi.id AND epi.istTVSendung = 1 order by Added desc";

		try {
			con = getConnection();
			ps = con.prepareStatement(sqlStr);
			ps.setInt(1, anzahl);
			rs = ps.executeQuery();
			vos = transformSet(rs);
		} catch (SQLException sqle) {
			throw new DataAccessException("Error executing " + sqlStr, sqle);
		} finally {
			try {
				// close the resources
				if (ps != null)
					ps.close();
				if (con != null)
					con.close();
			} catch (SQLException sqlee) {
				throw new DataAccessException("Can't close connection", sqlee);
			}
		}
		return vos;
	}

}
